package sgc.beans;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import net.sf.jasperreports.engine.JRException;
import sgc.struc.Pord_Header;
import sgc.struc.Pord_Position;

@ManagedBean(name = "purchaseOrderAprBean")
@SessionScoped
public class PurchaseOrderAprBean implements Serializable {

	private static final long serialVersionUID = 1L;
	private Pord_Header header;
	private List<Pord_Header> headers;
	private List<Pord_Header> filter_headers;
	private List<Pord_Position> positions;
	private DataSource ds;
	private int company_code;
	private String company_name;
	private String user_id;

	public PurchaseOrderAprBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		header = new Pord_Header();
		positions = new ArrayList<Pord_Position>();

		FacesContext context = FacesContext.getCurrentInstance();
		CheckUserBean cu = (CheckUserBean) context.getApplication()
				.evaluateExpressionGet(context, "#{checkUserBean}",
						CheckUserBean.class);

		company_code = cu.getUsuario().getCompany_code();
		company_name = cu.getUsuario().getCompany();
		user_id = cu.getUsuario().getName();
	}

	public Pord_Header getHeader() {
		return header;
	}

	public void setHeader(Pord_Header header) {
		this.header = header;
	}

	public List<Pord_Header> getHeaders() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getAllPorHeader_1(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.setString(2, "Pendiente");
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<Pord_Header> list = new ArrayList<Pord_Header>();

		while (result.next()) {
			Pord_Header aux_header = new Pord_Header();
			aux_header.setPor_no(result.getInt(2));
			aux_header.setCecos_name(result.getString("cecos_cod") + " - "
					+ result.getString("cecos_descrip"));
			aux_header.setFec_por(result.getDate("fec_por"));
			aux_header.setFec_req(result.getDate("fec_req"));
			aux_header.setPor_deta(result.getString("detail"));
			aux_header.setPor_no(result.getInt("por_no"));
			aux_header.setPreq_no(result.getInt("req_no"));
			aux_header.setSolicitante_name(result.getString("user"));
			aux_header.setStatus_name(result.getString("status_name"));
			aux_header.setSuplier_code(result.getInt("sup_code"));
			aux_header.setSuplier_name(result.getInt("sup_code") + " - "
					+ result.getString("sup_name3") + " "
					+ result.getString("sup_name4") + ", "
					+ result.getString("sup_name1") + " "
					+ result.getString("sup_name1"));
			list.add(aux_header);
		}
		con.close();

		return list;

	}

	public void setHeaders(List<Pord_Header> headers) {
		this.headers = headers;
	}

	public List<Pord_Position> getPositions() {
		return positions;
	}

	public void setPositions(List<Pord_Position> positions) {
		this.positions = positions;
	}

	private void Positions() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getPorPosition(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.setInt(2, header.getPor_no());
		cs.execute();
		ResultSet result = cs.getResultSet();
		positions.clear();
		while (result.next()) {
			Pord_Position aux_pos = new Pord_Position();

			aux_pos.setPor_no(result.getInt("por_no"));
			aux_pos.setPor_pos(result.getInt("por_pos"));
			aux_pos.setMat_cant(result.getDouble("mat_cant"));
			aux_pos.setMat_code(result.getInt("mat_code"));
			aux_pos.setMat_name(result.getInt("mat_code") + " - "
					+ result.getString("mat_name"));
			aux_pos.setMat_pend(result.getDouble("mat_pend"));
			aux_pos.setMat_unit(result.getString("unit"));
			positions.add(aux_pos);
		}
	}

	public String show() throws SQLException, JRException {
		Positions();
		return "OK";
	}

	public void save() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc;
		simpleProc = "{ CALL sp_updPordHeader(?,?,?,?) }";

		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.setInt(2, header.getPor_no());
		cs.setString(3, user_id);
		cs.setString(4, header.getStatus_name());

		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		String aux_message = result.getString(2);
		FacesContext.getCurrentInstance()
				.addMessage(
						null,
						new FacesMessage(FacesMessage.SEVERITY_INFO,
								aux_message, null));
		header = null;

	}
}
